
import json
import time
import random

import xlrd
from openpyxl import Workbook
import arrow
# import redis
import color_convert
import datetime


# redis_conn = redis.Redis(host='127.0.0.1', port=6379, decode_responses=True)
color_desc_map = {}

class my_color:
    def __init__(self, color_desc, color_class, color_rgb):
        self.desc = color_desc
        self.color_class = color_class
        self.rgb = color_rgb
        self.hex = color_convert.rgb_convert_hexstring(self.rgb)

    def __repr__(self) -> str:
        return (
            "desc: {}, class:{}, rgb:{}, hex:{}"
            .format(
                self.desc,
                self.color_class,
                self.rgb,
                self.hex,
            ))

# 解析所有颜色 分类存
def parse_color_table(excel_all_color_sheet):
    items = []
    nrow = excel_all_color_sheet.nrows
    for i in range(nrow):
        if i < 2:
            continue
        color_desc = excel_all_color_sheet.cell_value(i, 0)
        color_class = int(excel_all_color_sheet.cell_value(i, 1))
        color_l = int(excel_all_color_sheet.cell_value(i, 2))
        color_a = int(excel_all_color_sheet.cell_value(i, 3))
        color_b = int(excel_all_color_sheet.cell_value(i, 4))
        color_R = int(excel_all_color_sheet.cell_value(i, 6))
        color_G = int(excel_all_color_sheet.cell_value(i, 7))
        color_B = int(excel_all_color_sheet.cell_value(i, 8))
        rgb = ((color_R << 16) | (color_G << 8) | color_B)
        color_item = my_color(color_desc, color_class, rgb)
        items.append(color_item)
    return items


# 初始化颜色映射
def init_origin_color_map():
    global color_desc_map
    color_config_path = "./color_config.xls"
    ef = xlrd.open_workbook(color_config_path)
    all_color_sheet = ef.sheet_by_name("all_color")
    items = parse_color_table(all_color_sheet)
    # print(items)
    for item in items:
        color_desct = item.desc
        hex_color = item.hex
        if hex_color in color_desc_map.keys():
            continue
        color_desc_map[hex_color] = color_desct

    color_desc_map['#151314'] = '黑'
    color_desc_map['#fbfbfa'] = '白'
    print(color_desc_map)



def hex_color_cast_to_human(hex_color):
    return color_desc_map.get(hex_color, "undefine")


def hex_color_cast_to_human_for_list(hex_color_list):
    hl = []
    for e in hex_color_list:
        hl.append(hex_color_cast_to_human(e))
    return hl



# 显示随机目标颜色
class page_show_target_color:
    def __init__(self) -> None:
        self.time_ = 0
        self.color_ = ""
        self.color_class_ = 0
        self.bg_color_ = ""
    def debug(self):
        return "time = {0}, color = {1}, color_class = {2}, bg_color = {3}".format(self.time_, self.color_, self.color_class_, self.bg_color_)

    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()
    
# 随机颜色集合页面
class page_rand_color:
    def __init__(self) -> None:
        self.time_ = 0
        self.show_colors_ = []
    def debug(self):
        return "time = {0}, show_colors = {1}".format(self.time_, self.show_colors_)
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()

class page_test_color:
    def __init__(self) -> None:
        self.time_ = 0
        self.color_ = ""
        self.color_class_ = 0
        self.bg_color_ = ""
    def debug(self):
        return "time = {0}, color = {1}, color_class = {2}, bg_color = {3}".format(self.time_, self.color_, self.color_class_, self.bg_color_)
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()


class op_ack_color:
    def __init__(self) -> None:
        self.time_ = 0
        self.ack_ = ""
    def debug(self):
        return "time = {0}, ack = {1}".format(self.time_, self.ack_)
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()
        
class op_err_select:
    def __init__(self) -> None:
        self.time_ = 0
        self.err_list = []
    def debug(self):
        return "time = {0}, err_select = {1}".format(self.time_, self.err_list)
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()

class color_data_item:
    def __init__(self) -> None:
        self.mode_ = "default"
        self.index_ = 0
        self.page_show_target_color_ = page_show_target_color()
        self.page_rand_color_ = page_rand_color()
        self.page_test_color_ = page_test_color()
        self.op_ack_color_ = op_ack_color()
        self.op_err_select_ = op_err_select()
    def debug(self):
        return "index = {1}, page_show_target_color = {2}, page_rand_color = {3}, page_test_color = {4}, op_ack_color = {5}, op_err_select = {6}".format(
            self.mode_, self.index_, self.page_show_target_color_, self.page_rand_color_, self.page_test_color_, self.op_ack_color_, self.op_err_select_
        )
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()


class lab_data:
    def __init__(self) -> None:
        self.uid_ = 0
        self.mode_ = "default"
        self.item_list_ = []
    def debug(self):
        return "uid = {0}, mode = {1}, items = {2}".format(self.uid_, self.mode_, self.item_list_)
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()

# 根据索引写数据
# def fetch_data(index):
#     key = "queue:user:op"
#     v = redis_conn.lrange(key, index, index)
#     zv = json.loads(v)
#     return zv

def test():
    lines = []
    data = None
    with open("./wgy_lab1_20211117.data", "r") as f:
        for l in f:
            zv = json.loads(l)
            lines.append(zv)
    # lines.reverse()
    for d in lines:
        # print(d)
        item = handle_data(d)
        print(item)
        break

# 处理数据
def handle_data(dict_msg):
    lab_item = lab_data()
    uid = dict_msg.get("uid", 0)
    lab_item.uid_ = uid
    key = "op_select_mode"
    if key in dict_msg.keys():
        value = dict_msg[key]
        dv = json.loads(value)
        lab_item.mode_ = dv.get("select_mode", "default")

    for index in range(0, 100):
        item = color_data_item()
        item.index_ = index
        key = "page_show_target_color_" + str(index)
        if key in dict_msg.keys():
            value = dict_msg[key]
            dv = json.loads(value)
            item.page_show_target_color_.time_ = dv.get("time_ms", 0)
            item.page_show_target_color_.color_class_ = dv.get("t_color_class", 0)
            item.page_show_target_color_.color_ = dv.get("t_color", "")
            item.page_show_target_color_.bg_color_ = dv.get("bg_color", "")

        key = "page_rand_color_set_" + str(index)
        if key in dict_msg.keys():
            value = dict_msg[key]
            dv = json.loads(value)
            item.page_rand_color_.time_ = dv.get("time_ms", 0)
            op_table = dv.get("cp", [])
            origin_d = []
            if [] != op_table:
                origin_d = json.loads(op_table)
            item.page_rand_color_.show_colors_ = origin_d

        key = "page_test_color_" + str(index)
        if key in dict_msg.keys():
            value = dict_msg[key]
            dv = json.loads(value)
            item.page_test_color_.time_ = dv.get("time_ms", 0)
            item.page_test_color_.color_ = dv.get("color", "")
            item.page_test_color_.color_class_ = dv.get("color_class", 0)
            item.page_test_color_.bg_color_ = dv.get("bg_color", "")

        key = "op_err_select_" + str(index)
        if key in dict_msg.keys():
            value = dict_msg[key]
            dv = json.loads(value)
            item.op_err_select_.time_ = dv.get("time_ms", 0)
            l_err_list = dv.get("err_list", [])
            origin_d = []
            if [] != l_err_list:
                origin_d = json.loads(l_err_list)
            item.op_err_select_.err_list = origin_d

        key = "op_ack_color_" + str(index)
        if key in dict_msg.keys():
            value = dict_msg[key]
            dv = json.loads(value)
            item.op_ack_color_.time_ = dv.get("time_ms", 0)
            item.op_ack_color_.ack_ = dv.get("ack", "")
            lab_item.item_list_.append(item)


    return lab_item



# def test():
#     zv = fetch_data(0)
#     lab = handle_data(zv)
    
# test()


# 基本信息 uid mode index 
# 展示的目标对象 show_subject_time show_subject_color show_subject_color_class show_subject_bg_color
# 展示的颜色池 show_color_pool_time show_color_pool_object
# 选错的颜色 err_select_color 
# 展示的测试对象 show_test_subject_time show_test_subject_color show_test_subject_color_class show_test_subject_bg_color
# 确认的结果 ack_time ack


# def test_excel():
#     wb = Workbook()
#     ws = wb.active

# 读取原始数据
def read_origin_data():
    items = []
    lines = []
    data = None
    with open("./wgy_lab1_20211117.data", "r") as f:
        for l in f:
            zv = json.loads(l)
            lines.append(zv)
    lines.reverse()
    for d in lines:
        item = handle_data(d)
        items.append(item)
        # break
    return items


class struct_data:
    def __init__(self) -> None:
        self.uid = None
        self.mode = None
        self.index = None
        self.show_subject_time = None
        self.show_subject_color = None
        self.show_subject_color_class = None
        self.show_subject_bg_color = None
        self.show_color_pool_time = None
        self.show_color_pool_object = None
        self.err_select_commit_time = 0
        self.err_select_color = None
        self.show_test_subject_time = None
        self.show_test_subject_color = None
        self.show_test_subject_color_class = None
        self.show_test_subject_bg_color = None
        self.ack_time = None
        self.ack = None

        self.diff_time_select_1 = 0
        self.diff_time_select_2 = 0


    def title():
        t = []
        uid = "用户id"
        mode = "模式"
        index = "序号"
        start_time = "开始时间"
        show_subject_time = "显示目标的时间"
        show_subject_color = "显示目标的颜色"
        show_subject_color_class = "显示目标的颜色种类"
        show_subject_bg_color = "显示目标的颜色的背景颜色"
        show_color_pool_time = "显示颜色池的时间"
        show_color_pool_object = "颜色池"
        err_select_commit_time = "选错的颜色提交的时间"
        err_select_color = "选错的颜色"
        show_test_subject_time = "测试时的时间"
        show_test_subject_color = "测试时的颜色"
        show_test_subject_color_class = "测试时的颜色种类"
        show_test_subject_bg_color = "测试时的背景颜色"
        ack_time = "确认的时间"
        ack = "确认的标记"
        diff_time_select_1 = "选到正确颜色用时"
        diff_time_select_2 = "确认是否是正确颜色用时"

        t.append(uid)
        t.append(mode)
        t.append(index)
        t.append(start_time)
        t.append(show_subject_time)
        t.append(show_subject_color)
        t.append(show_subject_color_class)
        t.append(show_subject_bg_color)
        t.append(show_color_pool_time)
        t.append(show_color_pool_object)
        t.append(err_select_commit_time)
        t.append(err_select_color)
        t.append(show_test_subject_time)
        t.append(show_test_subject_color)
        t.append(show_test_subject_color_class)
        t.append(show_test_subject_bg_color)
        t.append(ack_time)
        t.append(ack)
        t.append(diff_time_select_1)
        t.append(diff_time_select_2)
        return t


    def cast_to_list(self):
        # if [] == self.show_color_pool_object:
        #     self.show_color_pool_object = []
            
        # if [] == self.err_select_color:
        #     self.err_select_color = ''

        l = []
        human_color = ""
        l.append(self.uid)
        l.append(self.mode)
        l.append(self.index)
        time_local = datetime.datetime.fromtimestamp(self.show_subject_time / 1000)
        dt = time_local.strftime("%Y-%m-%d %H:%M:%S.%f")
        l.append(dt) #开始时间
        l.append(dt) 
        
        l.append(hex_color_cast_to_human(self.show_subject_color))
        l.append(self.show_subject_color_class)
        l.append(hex_color_cast_to_human(self.show_subject_bg_color))
        time_local = datetime.datetime.fromtimestamp(self.show_color_pool_time / 1000)
        dt = time_local.strftime("%Y-%m-%d %H:%M:%S.%f")
        l.append(dt)
        l.append(self.show_color_pool_object)
        time_local = datetime.datetime.fromtimestamp(self.err_select_commit_time / 1000)
        dt = time_local.strftime("%Y-%m-%d %H:%M:%S.%f")
        l.append(dt)
        l.append(self.err_select_color)
        time_local = datetime.datetime.fromtimestamp(self.show_test_subject_time / 1000)
        dt = time_local.strftime("%Y-%m-%d %H:%M:%S.%f")
        l.append(dt)
        l.append(hex_color_cast_to_human(self.show_test_subject_color))
        l.append(self.show_test_subject_color_class)
        l.append(hex_color_cast_to_human(self.show_test_subject_bg_color))
        time_local = datetime.datetime.fromtimestamp(self.ack_time / 1000)
        dt = time_local.strftime("%Y-%m-%d %H:%M:%S.%f")
        l.append(dt)
        l.append(self.ack)
        self.diff_time_select_1 = self.err_select_commit_time - self.show_subject_time
        self.diff_time_select_2 = self.ack_time - self.show_test_subject_time
        l.append(self.diff_time_select_1)
        l.append(self.diff_time_select_2)
        return l

    def debug(self):
        return "uid = {0}, mode = {1}, index = {2}, show_subject_time = {3}, show_subject_color = {4}, show_subject_color_class = {5}, show_subject_bg_color = {6}, show_color_pool_time = {7}, show_color_pool_object = {8}, err_select_color = {9}, show_test_subject_time = {10}, show_test_subject_color = {11}, show_test_subject_color_class = {12}, show_test_subject_bg_color = {13}, ack_time = {14}, ack = {15}".format(
            self.uid,
            self.mode,
            self.index,
            self.show_subject_time,
            self.show_subject_color,
            self.show_subject_color_class,
            self.show_subject_bg_color,
            self.show_color_pool_time,
            self.show_color_pool_object,
            self.err_select_color,
            self.show_test_subject_time,
            self.show_test_subject_color,
            self.show_test_subject_color_class,
            self.show_test_subject_bg_color,
            self.ack_time,
            self.ack
        )
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()



# 二进制颜色数组转16进制颜色数组
def cast_bin_color_list_to_hex(bin_colors):
    hl = []
    # print(bin_colors)
    # print(type(bin_colors))
    for c in bin_colors:
        h = color_convert.rgb_convert_hexstring(c)
        hl.append(h)
    return hl

# 十六进制颜色list转成字符串
def hex_color_list_to_string(hex_color_list):
    s = ""
    l = len(hex_color_list)
    for i in range(l):
        s = s + hex_color_list[i]
        if (i + 1) != l:
            s = s + ", "

    return s

# 处理结构化数据
def handle_struct_data(items):
    ds = []
    for item in items:
        d = struct_data()
        d.uid = item.uid_
        d.mode = item.mode_
        for it in item.item_list_:
            d.index = it.index_
            d.show_subject_time = it.page_show_target_color_.time_
            d.show_subject_color = it.page_show_target_color_.color_
            d.show_subject_color_class = it.page_show_target_color_.color_class_
            d.show_subject_bg_color = it.page_show_target_color_.bg_color_

            d.show_color_pool_time = it.page_rand_color_.time_
            l_show_color_pool_object_hex = cast_bin_color_list_to_hex(it.page_rand_color_.show_colors_)
            l_show_color_pool_object_hum = hex_color_cast_to_human_for_list(l_show_color_pool_object_hex)
            d.show_color_pool_object = hex_color_list_to_string(l_show_color_pool_object_hum)
        
            d.err_select_commit_time = it.op_err_select_.time_
            l_err_select_color_hex = it.op_err_select_.err_list
            l_err_select_color_hum = hex_color_cast_to_human_for_list(l_err_select_color_hex)
            d.err_select_color = hex_color_list_to_string(l_err_select_color_hum)

            d.show_test_subject_time = it.page_test_color_.time_
            d.show_test_subject_color = it.page_test_color_.color_
            d.show_test_subject_color_class = it.page_test_color_.color_class_
            d.show_test_subject_bg_color = it.page_test_color_.bg_color_

            d.ack_time = it.op_ack_color_.time_
            d.ack = it.op_ack_color_.ack_
            # print(d)
            ds.append(d.cast_to_list())
    return ds

def handle_final_data(ds):
    wb = Workbook()
    ws = wb.active

    ws.append(struct_data.title())
    
    for d in ds:
        # print(d)
        ws.append(d)
    
    wb.save("wgy_test.xlsx")


def handle():
    items = read_origin_data()
    ds = handle_struct_data(items)
    handle_final_data(ds)




init_origin_color_map()
handle()